/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.x3.purchasing.inquery;

import com.x3.purchasing.common.ClassConnection;
import com.x3.purchasing.operator.Operator;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author kris
 */
public class InqueryDAOImpl implements InqueryDAO {

    public InqueryDAOImpl() throws SQLException {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public void insert(Inquery inquery) throws Exception {
        String sql = "INSERT INTO inquery(tanggal_buka,tanggal_tutup,judul,keterangan,operator_id) VALUES(?,?,?,?,?)";
        PreparedStatement ps = ClassConnection.getJdbc().getDataSource().getConnection().prepareStatement(sql);
        ps.setDate(1, new java.sql.Date(inquery.getTanggalBuka().getTime()));
        ps.setDate(2, new java.sql.Date(inquery.getTanggalTutup().getTime()));
        ps.setString(3, inquery.getJudul());
        ps.setString(4, inquery.getKeterangan());
        ps.setInt(5, inquery.getOperator().getId());
        ps.executeUpdate();
        ps.close();
    }

    public void update(Inquery inquery) throws Exception {
        String sql = "UPDATE inquery SET tanggal_buka=?,tanggal_tutup=?,judul=?,keterangan=?,operator_id=? WHERE id=?";
        PreparedStatement ps = ClassConnection.getJdbc().getDataSource().getConnection().prepareStatement(sql);
        ps.setDate(1, new java.sql.Date(inquery.getTanggalBuka().getTime()));
        ps.setDate(2, new java.sql.Date(inquery.getTanggalTutup().getTime()));
        ps.setString(3, inquery.getJudul());
        ps.setString(4, inquery.getKeterangan());
        ps.setInt(5, inquery.getOperator().getId());
        ps.setInt(6, inquery.getId());
        ps.executeUpdate();
        ps.close();
    }

    public void delete(int id) throws Exception {
        String sql = "DELETE FROM inquery WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{id});
    }

    public Inquery get(int id) throws Exception {
        Inquery inquery = null;
        String sql = "SELECT o.id as operator_id, i.* FROM inquery i"
                + " INNER JOIN operator o ON o.id=i.operator_id  WHERE i.id=?";
        inquery = (Inquery) ClassConnection.getJdbc().queryForObject(sql, new Object[]{id}, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Inquery temp = new Inquery();
                temp.setId(rs.getInt("id"));
                temp.setJudul(rs.getString("judul"));
                temp.setKeterangan(rs.getString("keterangan"));
                temp.setTanggalBuka(rs.getDate("tanggal_buka"));
                temp.setTanggalTutup(rs.getDate("tanggal_tutup"));

                Operator operator = new Operator();
                operator.setId(rs.getInt("operator_id"));

                temp.setOperator(operator);
                return temp;
            }
        });
        return inquery;
    }

    public List<Inquery> gets() throws Exception {
        List<Inquery> data = new ArrayList<Inquery>();
        String sql = "SELECT o.id as operator_id, i.* FROM inquery i"
                + " INNER JOIN operator o ON o.id=i.operator_id ORDER BY i.tanggal_buka DESC, i.id DESC";
        PreparedStatement ps = ClassConnection.getJdbc().getDataSource().getConnection().prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Inquery inquery = new Inquery();
            inquery.setId(rs.getInt("id"));
            inquery.setJudul(rs.getString("judul"));
            inquery.setKeterangan(rs.getString("keterangan"));
            inquery.setTanggalBuka(rs.getDate("tanggal_buka"));
            inquery.setTanggalTutup(rs.getDate("tanggal_tutup"));

            Operator operator = new Operator();
            operator.setId(rs.getInt("operator_id"));

            inquery.setOperator(operator);
            data.add(inquery);
        }
        rs.close();
        ps.close();
        return data;
    }

    public List<Inquery> gets(int start, int end) throws Exception {
        List<Inquery> data = new ArrayList<Inquery>();
        String sql = "SELECT o.id as operator_id, i.* FROM inquery i"
                + " INNER JOIN operator o ON o.id=i.operator_id ORDER BY i.tanggal_buka DESC, i.id DESC LIMIT 5";
       
        PreparedStatement ps = ClassConnection.getJdbc().getDataSource().getConnection().prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Inquery inquery = new Inquery();
            inquery.setId(rs.getInt("id"));
            inquery.setJudul(rs.getString("judul"));
            inquery.setKeterangan(rs.getString("keterangan"));
            inquery.setTanggalBuka(rs.getDate("tanggal_buka"));
            inquery.setTanggalTutup(rs.getDate("tanggal_tutup"));

            Operator operator = new Operator();
            operator.setId(rs.getInt("operator_id"));

            inquery.setOperator(operator);
            data.add(inquery);
        }
        rs.close();
        ps.close();
        return data;
    }

    public List<Inquery> gets(int accountId) throws Exception {
        List<Inquery> data = new ArrayList<Inquery>();
        String sql = "SELECT o.id as operator_id, i.*FROM inquery i"
                + " INNER JOIN operator o ON o.id = i.operator_id "
                + " INNER JOIN account a ON a.id = o.account_id "
                + " WHERE a.id = ?  ORDER BY i.tanggal_buka DESC";
        PreparedStatement ps = ClassConnection.getJdbc().getDataSource().getConnection().prepareStatement(sql);
        ps.setInt(1, accountId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Inquery inquery = new Inquery();
            inquery.setId(rs.getInt("id"));
            inquery.setJudul(rs.getString("judul"));
            inquery.setKeterangan(rs.getString("keterangan"));
            inquery.setTanggalBuka(rs.getDate("tanggal_buka"));
            inquery.setTanggalTutup(rs.getDate("tanggal_tutup"));

            Operator operator = new Operator();
            operator.setId(rs.getInt("operator_id"));

            inquery.setOperator(operator);
            data.add(inquery);
        }
        return data;
    }
}
